﻿using Business.Core.Annotations;
using Business.Data;
using Common;
using DataModel;
using Newtonsoft.Json;
using ProjectManager.User;
using ProjectManager.User.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FangPage.Data;
using Newtonsoft.Json.Converters;


namespace ProjectManagerSys
{
    public class IndexMerber : Common.BusinessBase
    {
        [Doc("统计", Group = "首页")]
        public virtual async Task<dynamic> TaskStatistics()
        {
            //object list = new object();
            try
            {
                string where = " 1=1 ";
                where += taskper("TASKLEADER", "TASKMEMBER"); //权限

                StringBuilder sql = new StringBuilder();
                
                sql.AppendFormat("select nvl(count(*), 0) as tasktotal, ");
                sql.AppendFormat("nvl(sum(CASE WHEN(TASKSTATUS <= 1 or TASKSTATUS = 6)  THEN 1 ELSE 0 END),0) as taskno, ");
                sql.AppendFormat("nvl(sum(CASE WHEN TASKSTATUS >= 2 and TASKSTATUS <= 3  and TOTALTIME > REALTIME and REALTIME is not null  THEN 1 ELSE 0 END),0) as taskup, ");
                sql.AppendFormat("nvl(sum(CASE WHEN TASKSTATUS >= 2 and TASKSTATUS <= 3 and TOTALTIME < REALTIME  THEN 1 ELSE 0 END),0) as taskover, ");
                sql.AppendFormat("nvl(sum(CASE WHEN TASKSTATUS >= 2 and TASKSTATUS <= 3   THEN 1 ELSE 0 END),0) as taskfinish ");
                sql.AppendFormat("from manager_task where active = 1 and {0} ", where);

                var list = DbHelper.ExcuteTable(sql.ToString());
                var alltask = DbHelper.ExcuteCount("select count(*) from manager_task where active = 1");//所有人的任务数，不需要再拼接条件
                var obj = new { list = list, alltask= alltask };
                return JsonConvert.SerializeObject(obj);

            }
            catch (Exception ex) { }

            return null;
        }

        [Doc("曲线图统计", Group = "首页")]
        public virtual async Task<dynamic> CurveStatic()
        {
            string where = " 1=1 ";
            List<series> list = new List<series>(); //返回页面的list数据
            List<string> xAxisData = new List<string>(); //曲线X轴的数据   
            StringBuilder sql = new StringBuilder();

            where += taskper("TASKLEADER", "TASKMEMBER"); //权限

            //全部任务
            sql.AppendFormat("select a.months,nvl(b.alltask,0) as alltask,nvl(b.finish,0) as finish,nvl(b.overdue,0) as overdue from ");
            sql.AppendFormat("(select TO_CHAR(ADD_MONTHS(TO_DATE('{0}', 'yyyy-MM-dd'), -LEVEL + 1), 'yyyy-MM') months FROM dual CONNECT BY LEVEL <= 12) a ",DateTime.Now.Date.AddMonths(1).ToString("yyyy-MM-dd"));
            sql.AppendFormat("LEFT JOIN ");
            sql.AppendFormat("(SELECT TO_CHAR(TASKSTARTTIME, 'YYYY-MM') as TIME, COUNT(*) AS alltask, ");
            sql.AppendFormat("sum(CASE WHEN TASKSTATUS >= 2 and TASKSTATUS <= 3 THEN 1 ELSE 0 END) as finish, ");
            sql.AppendFormat("sum(CASE WHEN TASKSTATUS >= 2 and TASKSTATUS <= 3 and TOTALTIME < REALTIME  THEN 1 ELSE 0 END) as overdue ");
            sql.AppendFormat("FROM manager_task where  ACTIVE = 1 and {0}  GROUP BY TO_CHAR(TASKSTARTTIME, 'YYYY-MM'))b ",where);
            sql.AppendFormat("on a.months = b.Time order by months ");
            var list2 = DbHelper.ExcuteList<MonthStatis>(sql.ToString());

            string[] arr = new string[]{ "alltask|", "finish|", "overdue|" };
            
            xAxisData = list2.Select(x => x.months).ToList();
            series oModel = new series() {
                data = list2.Select(x => x.alltask).ToList(),
                name = "我的任务"
            };
            list.Add(oModel);
            oModel = new series() {
                data = list2.Select(x => x.finish).ToList(),
                name = "完成任务"
            };
            list.Add(oModel);
            oModel = new series() {
                data = list2.Select(x => x.overdue).ToList(),
                name = "逾期任务"
            };
            list.Add(oModel);

            var obj = new { xAxisData = xAxisData , list = list };
            return JsonConvert.SerializeObject(obj);
        }

        [Doc("项目统计", Group = "首页")]
        public virtual async Task<dynamic> ProjectStatis()
        {
            string where = " 1=1 ";
            where += taskper("PROJECTLEADER", "PROJIECTMEMBER"); //项目表

            string where2 = " 1=1 ";
            where2 += taskper("TASKLEADER", "TASKMEMBER"); // 任务表

            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("select (CASE WHEN a.PROJECTNAME is not null THEN a.PROJECTNAME ELSE '未知项目' END) as PROJECTNAME,a.DAYS,a.id, b.* from((select * from manager_project where active = 1 and {0})a ", where);
            sql.Append("FULL OUTER JOIN ");
            sql.Append("(select PROJECTID, nvl(count(*), 0) as alltotal, ");
            sql.Append("nvl(sum(CASE WHEN TASKSTATUS >= 2 and TASKSTATUS <= 3 THEN 1 ELSE 0 END), 0) as finishtask, ");
            sql.Append("nvl(sum(CASE WHEN TASKSTATUS = 1 THEN 1 ELSE 0 END), 0) as beingtotal, ");
            sql.Append("nvl(sum(CASE WHEN TASKSTATUS >= 2 and TASKSTATUS <= 3  and TOTALTIME >= REALTIME THEN 1 ELSE 0 END), 0) as ontimetotal, ");
            sql.Append("nvl(sum(CASE WHEN TASKSTATUS >= 2 and TASKSTATUS <= 3  and OVERTIME > 0 THEN 1 ELSE 0 END), 0) as overtotal, ");
            sql.Append("nvl(sum(CASE WHEN TASKTYPE = 1 THEN TOTALTIME ELSE 0 END), 0) as kfallhous, ");
            sql.Append("nvl(sum(CASE WHEN TASKTYPE = 2 THEN TOTALTIME ELSE 0 END), 0) as ywallhous, ");
            sql.Append("nvl(sum(CASE WHEN TASKTYPE = 1 THEN REALTIME ELSE 0 END), 0) as kfrealhous, ");
            sql.Append("nvl(sum(CASE WHEN TASKTYPE = 2 THEN REALTIME ELSE 0 END), 0) as ywrealhous ");
            sql.AppendFormat("from manager_task where active = 1 and {0} ", where2);
            sql.Append("group by PROJECTID) b on a.id = b.PROJECTID) order by alltotal desc ");

            var list2 = DbHelper.ExcuteTable(sql.ToString());

            var result = new { code = 0, msg = "", count =list2.Rows.Count, data = list2 };

            IsoDateTimeConverter timeFormat = new IsoDateTimeConverter();
            timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
            return JsonConvert.SerializeObject(result, Formatting.Indented, timeFormat);
        }

        [Doc("用户统计", Group = "首页")]
        public virtual async Task<dynamic> UsersStatis()
        {
            
            var list2 = UsersStatis2();
            
            var result = new { code = 0, msg = "", count = list2.Rows.Count, data = list2 };

            IsoDateTimeConverter timeFormat = new IsoDateTimeConverter();
            timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
            return JsonConvert.SerializeObject(result, Formatting.Indented, timeFormat);
        }

        [Doc("计算个人饱和度", Group = "首页")]
        public virtual async Task<dynamic> CalculatePersonSat()
        {
            //object list = new object();
            var dt = new DataTable();
            try
            {
                dt = CalculatePersonSat2();
            }
            catch (Exception ex) { }
            return JsonConvert.SerializeObject(dt);
        }

        public string taskper(string search,string search2)
        {
            string where = "";
            AuthInfo authinfo = Logins.Authorize();
            if (authinfo.isadmin != 1)
            {
                var new_user = "," + authinfo.userid + ",";
                if (string.IsNullOrEmpty(authinfo.departs)) //个人
                {
                    where += string.Format(" and (CREATENAME='{0}' or UPDATENAME='{0}'  ", authinfo.userid);
                }
                else //部门
                {
                    var arr_depart = "'" + authinfo.departid.Replace(",", "','") + "'";
                    where += string.Format(" and (DEPARTCODE in({0}) ", arr_depart);
                }
                where += string.Format(" or {0} like '%{1}%' or {2} like '%{3}%') ", search, new_user,search2, new_user);
            }
            return where;
        }

        public partial class statis
        {
            public string TOTAL { get; set; }
            public string TYPE { get; set; }
        }

        public class MonthStatis
        {
            public string months { get; set; }
            public int alltask { get; set; }  
            public int finish { get; set; }
            public int overdue { get; set; }
        }

        /// <summary>
        /// 曲线图实体
        /// </summary>
        public class series
        {
            public string name { get; set; }
            public string type { get; set; }
            public List<int> data { get; set; }
            public string symbol { get; set; }//'none', //去掉折线图中的节点
            public bool smooth { get; set; } // false  //true 为平滑曲线，false为直线
            public series()
            {
                type = "line";
                smooth = true;
            }
        }

        /// <summary>
        /// 用户统计方法，写成方法，便于其他类的方法调用
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataTable UsersStatis2(string where="")
        {
            AuthInfo authinfo = Logins.Authorize();
            string where2 = " 1=1 ";
            if (authinfo.isadmin != 1)
            {
                if (string.IsNullOrEmpty(authinfo.departs)) //个人
                {
                    where2 += string.Format(" and a.id='{0}'  ", authinfo.userid);
                }
                else //部门
                {
                    var arr_depart = "'" + authinfo.departid.Replace(",", "','") + "'";
                    where2 += string.Format(" and a.DEPARTID in({0}) ", arr_depart);
                }
            }
            if (!string.IsNullOrEmpty(where))
            {
                where2 = where2 + where;
            }
            var list2 = new DataTable();
            StringBuilder sql = new StringBuilder();
            sql.Append("select max(a.realname) as realname ,a.id,count(a.id) as alltotal,sum(nvl(OVERTIMES,0)) as overtimes, ");
            sql.Append("nvl((select count(*) as allTask from manager_task where active=1),0) as allTask, ");
            sql.Append("sum(CASE WHEN TASKSTATUS >= 2 and TASKSTATUS <= 3 THEN 1 ELSE 0 END) as finishtask, ");
            sql.Append("sum(CASE WHEN TASKSTATUS >= 2 and TASKSTATUS <= 3 and TOTALTIME < REALTIME THEN 1 ELSE 0 END) as overtask, ");
            sql.Append("sum(CASE WHEN TASKSTATUS >= 2 and TASKSTATUS <= 3 and TOTALTIME > REALTIME and REALTIME is not null THEN 1 ELSE 0 END) as taskup, ");
            sql.Append("sum(CASE WHEN TASKSTATUS <= 1 or TASKSTATUS = 6 THEN 1 ELSE 0 END) as taskno, ");
            sql.Append("nvl(sum(CASE WHEN TASKSTATUS >= 2 and TASKSTATUS <= 3 THEN TOTALTIME ELSE 0 END), 0) as finishtask_totaltime, ");
            sql.Append("nvl(sum(CASE WHEN TASKSTATUS >= 2 and TASKSTATUS <= 3 THEN REALTIME ELSE 0 END), 0) as finishtask_realtime, ");
            sql.Append("to_number(trim(to_char(decode(nvl(sum(CASE WHEN TASKSTATUS >= 2 and TASKSTATUS <= 3 THEN REALTIME ELSE 0 END), 0), 0, 0, ");
            sql.Append("(1.00 / (nvl(sum(CASE WHEN TASKSTATUS >= 2 and TASKSTATUS <= 3 THEN REALTIME ELSE 0 END), 0) / ");
            sql.Append("nvl(sum(CASE WHEN TASKSTATUS >= 2 and TASKSTATUS <= 3 THEN TOTALTIME ELSE 0 END), 0))) * 100),'99999999999999.99'))) as uppercent, ");
            sql.AppendFormat("sum(CASE WHEN TASKSTATUS !=5 and TASKSTARTTIME <= to_date('{0}', 'yyyy-mm-dd hh24:mi:ss') THEN TOTALTIME ELSE 0 END) as totaltimes ",DateTime.Now);
            sql.Append("from bjsys_userinfo a,manager_task b ");
            sql.AppendFormat("where(b.TASKLEADER like CONCAT(CONCAT('%,', a.id), ',%') or b.TASKMEMBER like CONCAT(CONCAT('%,', a.id), ',%')) and a.active=1 and b.active=1 and {0} group by a.id ", where2);
            sql.Append(" order by alltotal desc ");

            try
            {
                list2 = DbHelper.ExcuteTable(sql.ToString());
            }
            catch (Exception ex) { }
            return list2;
        }

        /// <summary>
        /// 个人饱和度计算
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataTable CalculatePersonSat2(string start ="",string end="")
        {
            var dt = new DataTable();
            try
            {
                AuthInfo authinfo = Logins.Authorize();
                string where2 = " 1=1 ";
                if (authinfo.isadmin != 1)
                {
                    if (string.IsNullOrEmpty(authinfo.departs)) //个人
                    {
                        where2 += string.Format(" and a.id='{0}'  ", authinfo.userid);
                    }
                    else //部门
                    {
                        var arr_depart = "'" + authinfo.departid.Replace(",", "','") + "'";
                        where2 += string.Format(" and a.DEPARTID in({0}) ", arr_depart);
                    }
                }
                StringBuilder sql = new StringBuilder();

                sql.AppendFormat("select max(a.realname) as realname ,a.id,min(b.TASKSTARTTIME) as startTime from bjsys_userinfo a, manager_task b ");
                sql.AppendFormat("where (b.TASKLEADER like CONCAT(CONCAT('%,', a.id), ',%') or b.TASKMEMBER like CONCAT(CONCAT('%,', a.id), ',%')) and a.active = 1 and b.active = 1 and {0} group by a.id ", where2);

                dt = DbHelper.ExcuteTable(sql.ToString());
                dt.Columns.Add("alltimes");
                
                foreach (DataRow item in dt.Rows)
                {
                    double sat = 0; //个人能力
                    string userid = item["id"].ToString();
                    //减去调休的时间
                    List<MANAGER_LEAVE> leavelist = new List<MANAGER_LEAVE>();
                    using (var con = Data.DB.GetConnection())
                    {
                        if (start == "" && end == "") //没有查询的时候
                        {
                            if (!string.IsNullOrEmpty(item["starttime"].ToString()))
                            {
                                DateTime startTime = Convert.ToDateTime(item["starttime"]) < DateTime.Now ? Convert.ToDateTime(item["STARTTIME"]) : DateTime.Now;
                                DateTime endTime = Convert.ToDateTime(item["starttime"]) > DateTime.Now ? Convert.ToDateTime(item["STARTTIME"]) : DateTime.Now;
                                sat = new TaskMerber().CalculationWorkHour(startTime, endTime);
                                leavelist = con.MANAGER_LEAVE.Where(c => c.STARTTIME >= startTime && c.ENDTIME <= endTime && c.CREATENAME== userid).ToList() ;
                            }
                        }
                        else //查询规定时间的饱和度
                        {
                            sat = new TaskMerber().CalculationWorkHour(Convert.ToDateTime(start), Convert.ToDateTime(end).AddDays(1));
                            leavelist = con.MANAGER_LEAVE.Where(c => c.STARTTIME >= Convert.ToDateTime(start) && c.ENDTIME <= Convert.ToDateTime(end).AddDays(1) && c.CREATENAME == userid).ToList();
                        }
                        
                    }
                    var leavetotal = (double)leavelist.Sum(c=>c.LEAVETIMES);
                    
                    item["alltimes"] = sat-leavetotal; //数据类型为 文本
                }
            }
            catch (Exception ex) { }

            return dt;
        }
    }
}
